package com.study.model.jdao;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.study.model.pojo.Emp;


public interface EmpJdao extends CrudRepository<Emp, Integer>{

	/**
	 * 查询所有员工*/
	public List<Emp> findAll();
	
	/**
	 * 根据员工ID和密码查询员工*/
	@Query("from Emp e where e.eid = ?1 and e.pwd = ?2 and e.state=0")
	public Emp queryByeidAndpwd(int eid, String pwd);
	
	/**
	 * 查询没有部门的员工(且job不是boss)*/
	@Query("from Emp e where e.deptno is null and e.state=0 and e.job!='boss'")
	public List<Emp> findWithoutdept();
	/**
	 * 查询所有的经理*/
	@Query("from Emp e where e.state=0 and e.job='manager'")
	public List<Emp> findManager();
	
	/**
	 * 根据部门ID查询该部门下所有员工*/
	@Query("from Emp e where e.state=0 and e.job='stuff' and e.deptno = ?1")
	public List<Emp> findAllbydept(int deptno);
	
	/**
	 * 查询没有部门的员工*/
	@Query(" FROM Emp e WHERE e.state=0 and e.deptno= ?1 AND e.job='stuff' AND e.eid NOT IN(SELECT e.eid FROM Rights r,Empwithright em,e  WHERE  r.rid=em.rid AND e.eid=em.eid AND r.deptno= ?1)")
	public List<Emp> findNoright(int deptno);
	
	/**
	 * 查询没有这个权限部门的员工*/
	@Query(" FROM Emp e WHERE e.state=0 and e.deptno= ?1 AND e.job='stuff' AND e.eid NOT IN(SELECT e.eid FROM Rights r,Empwithright em,e  WHERE  r.rid=em.rid AND e.eid=em.eid AND r.deptno= ?1 AND r.rid=?2)")
	public List<Emp> findNothisright(int deptno,int rid);
	/**
	 * 查询在此部门的有权限的员工*/
	@Query(" SELECT e FROM Emp e,Rights r,Empwithright em WHERE  e.state=0 and r.rid=em.rid AND e.eid=em.eid AND r.deptno= ?1 GROUP BY e.eid HAVING COUNT(1)=1")
	public List<Emp> findhasright(int deptno);
	/**
	 * 部门经理*/
	@Query("SELECT e.eid FROM Emp e WHERE job='manager' AND deptno=(SELECT e.deptno FROM e WHERE eid=?1)")
	public Integer findMng(int eid);
	
	@Query("SELECT e.ename FROM Emp e WHERE e.eid=?1")
	public String findEnamebyEid(int eid);
	
	@Query("SELECT e.job FROM Emp e WHERE e.eid=?1")
	public String findJobbyEid(int eid);
	/**
	 * 查找BOSS*/
	@Query("SELECT e.eid FROM Emp e WHERE e.job='boss'")
	public Integer findBOSS();
	
	@Query("from Emp e where e.job != 'boss' and e.state=0")
	public List<Emp> queryNoboss();
}
